{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "import os\n",
    "import psycopg2\n",
    "import pandas as pd\n",
    "from IPython.display import display\n",
    "sys.path.insert(0, os.path.realpath('..'))\n",
    "import django\n",
    "django.setup()\n",
    "\n",
    "conn = psycopg2.connect(\"service=firecares\")\n",
    "\n",
    "# Handles the cases where geoms appear to be 3857 instead of 4326 (lat/lon)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>state</th>\n",
       "      <th>st_x</th>\n",
       "      <th>st_y</th>\n",
       "      <th>st_x</th>\n",
       "      <th>st_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [id, state, st_x, st_y, st_x, st_y]\n",
       "Index: []"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q = \"\"\"\n",
    "select id, state, ST_X(geom), ST_Y(geom), ST_X(ST_Transform(ST_SetSRID(geom, 3857), 4326)), ST_Y(ST_Transform(ST_SetSRID(geom, 3857), 4326))\n",
    "from firestation_usgsstructuredata\n",
    "where ST_X(geom) > 180 or ST_Y(geom) > 180 or ST_X(geom) < -180 or ST_Y(geom) < -180;\n",
    "\"\"\"\n",
    "\n",
    "pd.read_sql_query(q, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "q = \"\"\"\n",
    "update firestation_usgsstructuredata\n",
    "set geom = ST_Transform(ST_SetSRID(geom, 3857), 4326)\n",
    "where ST_X(geom) > 180 or ST_Y(geom) > 180 or ST_X(geom) < -180 or ST_Y(geom) < -180;\n",
    "\"\"\"\n",
    "\n",
    "c = conn.cursor()\n",
    "c.execute(q)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"../stations.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(-9383067.4697, 4060599.1174999997) vs -84.2893848662,34.2365294033\n",
      "(-9390790.9553, 4062443.507100001) vs -84.3589104511,34.2501706846\n",
      "(-9406400.3388, 4052058.2250000015) vs -84.4991319293,34.1730208322\n",
      "(-9405741.5571, 4064187.5003999993) vs -84.4932139923,34.2631194789\n",
      "(-9412587.7301, 4071276.2192) vs -84.5545718401,34.3156841291\n",
      "(-9418820.7172, 4058302.6202000007) vs -84.6107060862,34.2194174634\n",
      "(-9381786.893, 4077873.1225000024) vs -84.2777984389,34.3649880544\n",
      "(-9416619.9789, 4072767.869400002) vs -84.5906056072,34.3265697503\n",
      "(-9420685.3844, 4081947.3716999963) vs -84.6274566766,34.3948693327\n",
      "(-9397934.8774, 4067876.3337000012) vs -84.4230853956,34.290501745\n",
      "(-9411482.4055, 4053414.3778000027) vs -84.5447849108,34.1830993863\n",
      "(-9397167.4376, 4051148.590499997) vs -84.4161913666,34.1662600055\n",
      "(-9393201.7564, 4052114.4641999975) vs -84.3805670456,34.1734388108\n",
      "(-9415903.658, 4047491.0276999995) vs -84.5844337441,34.1391963196\n",
      "(-9420215.0105, 4041671.8396999985) vs -84.6232312363,34.0957921813\n",
      "(-9405721.6483, 4054997.6328999996) vs -84.4930351488,34.194864178\n",
      "(-9381025.5182, 4057376.714400001) vs -84.2711860372,34.2125394807\n",
      "(-9418530.9295, 4069826.3649000004) vs -84.6079347593,34.3051914537\n",
      "(-9392090.7241, 4080036.833800003) vs -84.3706010528,34.3808498388\n",
      "(-9407725.6633, 4047739.3238999993) vs -84.5110375219,34.1409159306\n",
      "(-9415408.9922, 4042153.4390999973) vs -84.5800580394,34.0993747055\n",
      "(-9407192.2282, 4056824.275899999) vs -84.5062455928,34.2084354887\n",
      "(-9393789.5081, 4073006.563000001) vs -84.3858469091,34.3285686841\n",
      "(-9400678.5072, 4054922.275899999) vs -84.4477318415,34.1943042549\n",
      "(-9405446.006, 4073268.1525000036) vs -84.4905590118,34.3305092487\n",
      "(-9405612.508, 4060697.341799997) vs -84.4920547247,34.237203768\n",
      "(-9402833.8929, 4043210.709399998) vs -84.4668956256,34.1072251709\n"
     ]
    }
   ],
   "source": [
    "from firecares.firestation.models import FireStation\n",
    "\n",
    "for s in df[['X', 'Y', 'StationNum']].to_dict(orient='records'):\n",
    "    fs = FireStation.objects.filter(department_id=77286, station_number=s.get('StationNum')).first()\n",
    "    if not fs and s.get('StationNum') == 27:\n",
    "        fs = FireStation.objects.get(id=1521)\n",
    "        \n",
    "    print '{} vs {},{}'.format(fs.geom.coords, s.get('X'), s.get('Y')) \n",
    "    fs.geom.x = s.get('X')\n",
    "    fs.geom.y = s.get('Y')\n",
    "    fs.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>StationNum</th>\n",
       "      <th>StationNam</th>\n",
       "      <th>FullAddr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-84.289385</td>\n",
       "      <td>34.236529</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>Holbrook Campground - Hwy 372</td>\n",
       "      <td>9253 Freehome Highway Canton 30114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-84.358910</td>\n",
       "      <td>34.250171</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>Circle Five</td>\n",
       "      <td>1037 East Cherokee Drive Canton 30115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-84.499132</td>\n",
       "      <td>34.173021</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>Holly Springs</td>\n",
       "      <td>260 Hickory Road Canton 30115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-84.493214</td>\n",
       "      <td>34.263119</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>Old North Canton</td>\n",
       "      <td>1398 Reinhardt College Parkway Canton 30114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-84.554572</td>\n",
       "      <td>34.315684</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>Waleska City</td>\n",
       "      <td>9081 Fincher Road Waleska 30183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-84.610706</td>\n",
       "      <td>34.219417</td>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>Sutallee - Hwy 20 West</td>\n",
       "      <td>2833 Knox Bridge Highway Canton 30114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>-84.277798</td>\n",
       "      <td>34.364988</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "      <td>Mica</td>\n",
       "      <td>5804 Yellow Creek Road Ball Ground 30107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-84.590606</td>\n",
       "      <td>34.326570</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>Lake Arrowhead - Front Gate</td>\n",
       "      <td>125 Chickasaw Drive Waleska 30183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>-84.627457</td>\n",
       "      <td>34.394869</td>\n",
       "      <td>17</td>\n",
       "      <td>18</td>\n",
       "      <td>Salacoa</td>\n",
       "      <td>5840 Salacoa Road Waleska 30183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>-84.423085</td>\n",
       "      <td>34.290502</td>\n",
       "      <td>19</td>\n",
       "      <td>21</td>\n",
       "      <td>Ball Ground Industrial</td>\n",
       "      <td>1190 Evenflo Drive Ball Ground 30107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>-84.544785</td>\n",
       "      <td>34.183099</td>\n",
       "      <td>20</td>\n",
       "      <td>22</td>\n",
       "      <td>Bridgemill</td>\n",
       "      <td>9550 Bells Ferry Road Canton 30114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>-84.416191</td>\n",
       "      <td>34.166260</td>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>Hickory Flat - Vaughn Road</td>\n",
       "      <td>7625 Vaughn Road Canton 30115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>-84.380567</td>\n",
       "      <td>34.173439</td>\n",
       "      <td>23</td>\n",
       "      <td>32</td>\n",
       "      <td>Hickory Flat - Sugar Pike Rd</td>\n",
       "      <td>3644 Sugar Pike Road Canton 30115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>-84.584434</td>\n",
       "      <td>34.139196</td>\n",
       "      <td>26</td>\n",
       "      <td>20</td>\n",
       "      <td>Oak Grove - Bells Ferry Hobgood</td>\n",
       "      <td>6724 Bells Ferry Road Woodstock 30189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>-84.623231</td>\n",
       "      <td>34.095792</td>\n",
       "      <td>27</td>\n",
       "      <td>19</td>\n",
       "      <td>Oak Grove - Hwy 92 West</td>\n",
       "      <td>100 Ridge Mill Court Acworth 30102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>-84.493035</td>\n",
       "      <td>34.194864</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "      <td>Headquarters</td>\n",
       "      <td>150 Chattin Drive Canton 30115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>-84.271186</td>\n",
       "      <td>34.212539</td>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "      <td>Holbrook Campground Rd</td>\n",
       "      <td>2250 Holbrook Campground Road Alpharetta 30004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>-84.607935</td>\n",
       "      <td>34.305191</td>\n",
       "      <td>29</td>\n",
       "      <td>27</td>\n",
       "      <td>Lake Arrowhead Vfd - Rear Gate</td>\n",
       "      <td>7010 Great Festival Trl Waleska 30103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>-84.370601</td>\n",
       "      <td>34.380850</td>\n",
       "      <td>30</td>\n",
       "      <td>26</td>\n",
       "      <td>Nelson City</td>\n",
       "      <td>89 Dogwood Pass Nelson 30107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>-84.511038</td>\n",
       "      <td>34.140916</td>\n",
       "      <td>24</td>\n",
       "      <td>24</td>\n",
       "      <td>Woodstock/South Holly Springs</td>\n",
       "      <td>1000 Riverpark Boulevard Woodstock 30188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>-84.580058</td>\n",
       "      <td>34.099375</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>Oak Grove</td>\n",
       "      <td>100 Old Bascomb Ct Acworth 30102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>-84.506246</td>\n",
       "      <td>34.208435</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>CCFES South Canton</td>\n",
       "      <td>2371 Marietta Highway Canton 30115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>-84.385847</td>\n",
       "      <td>34.328569</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Ball Ground</td>\n",
       "      <td>420 Valley St Ball Ground 30107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>-84.447732</td>\n",
       "      <td>34.194304</td>\n",
       "      <td>22</td>\n",
       "      <td>3</td>\n",
       "      <td>Hickory Flat - 3</td>\n",
       "      <td>270 Crown Ave Canton 30115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>-84.490559</td>\n",
       "      <td>34.330509</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>Clayton Community</td>\n",
       "      <td>3396 Land Road Canton 30114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>-84.492055</td>\n",
       "      <td>34.237204</td>\n",
       "      <td>46</td>\n",
       "      <td>16</td>\n",
       "      <td>Canton City Downtown</td>\n",
       "      <td>190 West Main Street Canton 30114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>-84.466896</td>\n",
       "      <td>34.107225</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>Little River</td>\n",
       "      <td>1530 Barnes Road Woodstock 30188</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            X          Y  OBJECTID  StationNum  \\\n",
       "0  -84.289385  34.236529         3           4   \n",
       "1  -84.358910  34.250171         4           5   \n",
       "2  -84.499132  34.173021         7           8   \n",
       "3  -84.493214  34.263119         8           9   \n",
       "4  -84.554572  34.315684        11          12   \n",
       "5  -84.610706  34.219417        12          13   \n",
       "6  -84.277798  34.364988        14          15   \n",
       "7  -84.590606  34.326570        16          17   \n",
       "8  -84.627457  34.394869        17          18   \n",
       "9  -84.423085  34.290502        19          21   \n",
       "10 -84.544785  34.183099        20          22   \n",
       "11 -84.416191  34.166260        21          23   \n",
       "12 -84.380567  34.173439        23          32   \n",
       "13 -84.584434  34.139196        26          20   \n",
       "14 -84.623231  34.095792        27          19   \n",
       "15 -84.493035  34.194864         1          99   \n",
       "16 -84.271186  34.212539        25          25   \n",
       "17 -84.607935  34.305191        29          27   \n",
       "18 -84.370601  34.380850        30          26   \n",
       "19 -84.511038  34.140916        24          24   \n",
       "20 -84.580058  34.099375        18           1   \n",
       "21 -84.506246  34.208435        10          11   \n",
       "22 -84.385847  34.328569         2           2   \n",
       "23 -84.447732  34.194304        22           3   \n",
       "24 -84.490559  34.330509         5           6   \n",
       "25 -84.492055  34.237204        46          16   \n",
       "26 -84.466896  34.107225         6           7   \n",
       "\n",
       "                         StationNam  \\\n",
       "0     Holbrook Campground - Hwy 372   \n",
       "1                       Circle Five   \n",
       "2                     Holly Springs   \n",
       "3                  Old North Canton   \n",
       "4                      Waleska City   \n",
       "5            Sutallee - Hwy 20 West   \n",
       "6                              Mica   \n",
       "7       Lake Arrowhead - Front Gate   \n",
       "8                           Salacoa   \n",
       "9            Ball Ground Industrial   \n",
       "10                       Bridgemill   \n",
       "11       Hickory Flat - Vaughn Road   \n",
       "12     Hickory Flat - Sugar Pike Rd   \n",
       "13  Oak Grove - Bells Ferry Hobgood   \n",
       "14          Oak Grove - Hwy 92 West   \n",
       "15                     Headquarters   \n",
       "16           Holbrook Campground Rd   \n",
       "17   Lake Arrowhead Vfd - Rear Gate   \n",
       "18                      Nelson City   \n",
       "19    Woodstock/South Holly Springs   \n",
       "20                        Oak Grove   \n",
       "21               CCFES South Canton   \n",
       "22                      Ball Ground   \n",
       "23                 Hickory Flat - 3   \n",
       "24                Clayton Community   \n",
       "25             Canton City Downtown   \n",
       "26                     Little River   \n",
       "\n",
       "                                          FullAddr  \n",
       "0               9253 Freehome Highway Canton 30114  \n",
       "1            1037 East Cherokee Drive Canton 30115  \n",
       "2                    260 Hickory Road Canton 30115  \n",
       "3      1398 Reinhardt College Parkway Canton 30114  \n",
       "4                  9081 Fincher Road Waleska 30183  \n",
       "5            2833 Knox Bridge Highway Canton 30114  \n",
       "6         5804 Yellow Creek Road Ball Ground 30107  \n",
       "7                125 Chickasaw Drive Waleska 30183  \n",
       "8                  5840 Salacoa Road Waleska 30183  \n",
       "9             1190 Evenflo Drive Ball Ground 30107  \n",
       "10              9550 Bells Ferry Road Canton 30114  \n",
       "11                   7625 Vaughn Road Canton 30115  \n",
       "12               3644 Sugar Pike Road Canton 30115  \n",
       "13           6724 Bells Ferry Road Woodstock 30189  \n",
       "14              100 Ridge Mill Court Acworth 30102  \n",
       "15                  150 Chattin Drive Canton 30115  \n",
       "16  2250 Holbrook Campground Road Alpharetta 30004  \n",
       "17           7010 Great Festival Trl Waleska 30103  \n",
       "18                    89 Dogwood Pass Nelson 30107  \n",
       "19        1000 Riverpark Boulevard Woodstock 30188  \n",
       "20                100 Old Bascomb Ct Acworth 30102  \n",
       "21              2371 Marietta Highway Canton 30115  \n",
       "22                 420 Valley St Ball Ground 30107  \n",
       "23                      270 Crown Ave Canton 30115  \n",
       "24                     3396 Land Road Canton 30114  \n",
       "25               190 West Main Street Canton 30114  \n",
       "26                1530 Barnes Road Woodstock 30188  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(-84.58005803935441, 34.0993747054522),\n",
       " (-84.62323123632191, 34.095792181300205),\n",
       " (-84.2711860371648, 34.2125394807089),\n",
       " (-84.385846909144, 34.3285686841128),\n",
       " (-84.4230853956422, 34.2905017450316),\n",
       " (-84.2777984388736, 34.364988054372894),\n",
       " (-84.37060105281749, 34.380849838821),\n",
       " (-84.5447849108352, 34.1830993862808),\n",
       " (-84.4920547246762, 34.2372037680391),\n",
       " (-84.5062455928134, 34.2084354887498),\n",
       " (-84.2893848662313, 34.2365294032759),\n",
       " (-84.3589104510761, 34.2501706845755),\n",
       " (-84.4905590117725, 34.3305092487104),\n",
       " (-84.49303514881059, 34.1948641779575),\n",
       " (-84.447731841461, 34.1943042549195),\n",
       " (-84.3805670456389, 34.1734388108431),\n",
       " (-84.4161913665757, 34.166260005525),\n",
       " (-84.4991319292805, 34.1730208321629),\n",
       " (-84.493213992319, 34.2631194789143),\n",
       " (-84.6107060861765, 34.2194174634246),\n",
       " (-84.5545718400793, 34.3156841291121),\n",
       " (-84.5906056071791, 34.3265697502822),\n",
       " (-84.6079347593216, 34.3051914536837),\n",
       " (-84.6274566765919, 34.3948693327083),\n",
       " (-84.46689562564609, 34.1072251708984),\n",
       " (-84.58443374409359, 34.1391963196169),\n",
       " (-84.51103752188509, 34.1409159305985)]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "map(lambda x: x.get('geom').coords, FireStation.objects.filter(department_id=77286).values('geom', 'station_number'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
